/**
 * @description: 
 * @version: 0.0.1
 * @author: ljr
 * @Date: 2023-09-13 14:07:35
 */

import { Provide } from '@midwayjs/core';
import { ProOrderContact } from './project_order_contact.entity';
import { InjectRepository, InjectDataSource } from '@midwayjs/sequelize';
import { Repository, Sequelize } from 'sequelize-typescript';

@Provide()
export class ProjectOrderContactModel {
  @InjectRepository(ProOrderContact)
  photoRepository: Repository<ProOrderContact>;

  @InjectDataSource()
  executeRawSQL: Sequelize;

  /** 
   * 新增项目和订单关联关系
   * @param {*} projId  项目id
   * @param {*} orderId 订单id
   * @returns 
   */
  async insertContact(params: any): Promise<any> {
    const sql = `INSERT INTO project_order_contact SET proj_id = ${params.projId}, order_id = ${params.orderId}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
    * 查询当前关联关系是否存在
    * @param {*} projId  项目id
    * @param {*} orderId 订单id
    * @returns 
    */
  async countContact(params: any): Promise<any> {
    const sql = `select count(1) as total from project_order_contact where proj_id = ${params.projId} and order_id = ${params.orderId}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
    * 查询当前项目是否已关联订单
    * @param {*} projId  项目id
    * @returns 
    */
  async countContactByProj(params: any): Promise<any> {
    const sql = `select count(1) as total from project_order_contact where proj_id = ${params.projId}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
    * 删除指定项目和订单关联数据
    * @param {*} projId  项目id
    * @returns 
    */
  async delOrderContact(params: any): Promise<any> {
    const sql = `delete from project_order_contact where proj_id = ${params.projId} and order_id = ${params.orderId}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
    * 删除指定项目关联数据
    * @param {*} projId  项目id
    * @returns 
    */
  async delContact(projId: number): Promise<any> {
    const sql = `delete from project_order_contact where proj_id = ${projId}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
  * 查询当前项目关联的订单数据
  * @param {*} projId  项目id
  * @returns 
  */
  async getOrderDataByProjId(projId: number): Promise<any> {
    const sql = `SELECT
        o.* 
    FROM
        project_order_contact c
        LEFT JOIN orders_records o ON c.order_id = o.id 
    WHERE
        c.proj_id = ${projId}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
  * 查询当前订单id是否关联项目信息
  * @param {*} orderId  订单id
  * @returns 
  */
  async getCountByOrderId(orderId: number): Promise<any> {
    const sql = `select count(1) as total from project_order_contact where order_id = ${orderId}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
  * 查询当前订单关联的项目数据
  * @param {*} orderId  订单id
  * @returns 
  */
  async getOrderDataByOrderId(orderId: number): Promise<any> {
    // const sql = `SELECT
    //   w.*
    // FROM
    //   project_order_contact c
    //   LEFT JOIN working_records w ON c.proj_id = w.projects_id
    // WHERE
    //   c.order_id = ${orderId} order by w.user_nick desc`;

    const sql = `SELECT
        w.projects_id,
        w.projects_name,
        SUM(w.work_amount * w.hours_amount) AS total_amount
      FROM
        project_order_contact c
        LEFT JOIN working_records w ON c.proj_id = w.projects_id
      WHERE
        c.order_id = ${orderId} GROUP BY w.projects_id order by w.projects_id desc`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

}